package com.e3.basdoc.region.dao;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import com.e3.basdoc.region.entity.RegionEntity;
import com.e3.basdoc.region.model.RegionModel;
import com.e3.basdoc.region.model.RegionQC;
import com.e3.core.base.SuperDAO;

/**
 * Description: <br>
 * 
 * @author: <a href="mailto:senton1101@126.com">TianXiangdong</a> 2010-8-6
 */
public class RegionDAO extends SuperDAO {

	private String querySQL = "SELECT t FROM RegionEntity t";
	
	private String nativeQuerySQL = "SELECT t.id,t.code,t.name,t.english_name,t.zipcode,t.parent_id,(select count(n.id) from t_region n where n.parent_id=t.id) as childrenSize FROM t_region t";

	public List<RegionEntity> getRegionsByCondition(RegionQC regionQC) {
		String sql = querySQL;
		regionQC.setPrefix("t");
		String whereSQL = regionQC.getWhereSQL();
		if (whereSQL != null && whereSQL.length() > 0) {
			sql = sql + " WHERE " + whereSQL;
		}
		return this.find(sql);
	}
	
	@SuppressWarnings("rawtypes")
	public List<Long> getRegionIdsByCondition(RegionQC regionQC) {
		String sql = "SELECT t.id FROM RegionEntity t";
		regionQC.setPrefix("t");
		String whereSQL = regionQC.getWhereSQL();
		if (whereSQL != null && whereSQL.length() > 0) {
			sql = sql + " WHERE " + whereSQL;
		}
		List result = executeHQLQuery(sql, null);
		if(result!=null){
			List<Long> ids = new ArrayList<Long>(result.size());
			for (Object object : result) {
				ids.add((Long)object);
			}
			return ids;
		}
		return null;
	}

	public void deleteRegionByCodes(List<RegionModel> regionModels) {
		if (regionModels == null || regionModels.isEmpty()) {
			return;
		}
		String[] codes = new String[regionModels.size()];
		for (int i = 0; i < regionModels.size(); i++) {
			codes[i] = regionModels.get(i).getCode();
		}
		this.deleteAllBySingleField(RegionEntity.class, "code", codes);
	}

	public void deleteRegionByIds(Long[] ids) {
		this.deleteAllBySingleField(RegionEntity.class, "id", ids);
	}

	public void deleteRegionChildren(Long[] parentIds) {
		if (parentIds == null || parentIds.length == 0) {
			return;
		}
		this.deleteAllBySingleField(RegionEntity.class, "parentId", parentIds);
	}

	public RegionEntity getRegionByCode(String code) {
		String whSql = " WHERE t.code = ?";
		String sql = querySQL + whSql;
		List<RegionEntity> entities = this.find(sql, code);
		return (entities == null || entities.size() == 0) ? null : entities.get(0);
	}

	public long childrenCount(long pId) {
		String sql = "SELECT count(t) FROM RegionEntity t WHERE t.parentId = ?";
		return this.counter(sql, pId);
	}
	
	@SuppressWarnings("unchecked")
	public List<RegionModel> getAllRegions(){
		List<RegionEntity> result = this.executeNativeQuery(nativeQuerySQL,null);
		return resultToRegionModel(result);
	}
	
	@SuppressWarnings("rawtypes")
	public List<Long> getChildrenIds(Long[] parentIds){
		if(parentIds==null||parentIds.length==0){
			return null;
		}
		String querySQL = "SELECT m.id FROM t_region m WHERE ";
		StringBuffer where = new StringBuffer();
		int length = parentIds.length;
		for (int i=0;i<length;i++) {
			long id = parentIds[i];
			if(i==length-1){
				where.append("m.parent_id=" + id);
			}
			else{
				where.append("m.parent_id=" + id+" OR ");
			}
		}
		
		String sql = querySQL + where.toString();
		List result = this.executeNativeQuery(sql ,null);
		if(result!=null){
			List<Long> ids = new ArrayList<Long>();
			for (Object object : result) {
				BigInteger bi = (BigInteger)object;
				ids.add(bi.longValue());
			}
			return ids;
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public List<RegionModel> getRegionChildren(long parentId){
		String querySQL = nativeQuerySQL + " WHERE t.parent_id=?";
		List<RegionEntity> result = this.executeNativeQuery(querySQL,new Object[]{parentId});
		return resultToRegionModel(result);
	}

	private List<RegionModel> resultToRegionModel(List<RegionEntity> result) {
		if(result!=null){
			List<RegionModel> regions = new ArrayList<RegionModel>(result.size());
			for (Object object : result) {
				Object[] values = (Object[])object;
				RegionModel model = new RegionModel();
				model.setId(((BigInteger)values[0]).longValue());
				model.setCode((String)values[1]);
				model.setName((String)values[2]);
				
				model.setEnglishName((String)values[3]);
				model.setZipcode(values[4]+"");
				model.setParentId(((BigInteger)values[5]).longValue());
				model.setHasChildren(((BigInteger)values[6]).intValue()>0);
				regions.add(model);
			}
			return regions;
		}
		return null;
	}

	@SuppressWarnings("rawtypes")
	public List<Long> getPagingRegionIdsByCondition(RegionQC regionQC,int start,int max) {
		String sql = "SELECT t.id FROM RegionEntity t";
		if(regionQC != null){
			regionQC.setPrefix("t");
			String whereSQL = regionQC.getWhereSQL();
			if (whereSQL != null && whereSQL.length() > 0) {
				sql = sql + " WHERE " + whereSQL;
			}
		}
		List result = executePagingHQLQuery(sql, null,start,max);
		if(result!=null){
			List<Long> ids = new ArrayList<Long>(result.size());
			for (Object object : result) {
				ids.add((Long)object);
			}
			return ids;
		}
		return null;
	} 

	public long counterByCondition(RegionQC regionQC){
		String sql = "SELECT count(t) FROM RegionEntity t";
		if(regionQC!=null){
			regionQC.setPrefix("t");
			String whereSQL = regionQC.getWhereSQL();
			if (whereSQL != null && whereSQL.length() > 0) {
				sql = sql + " WHERE " + whereSQL;
			}
		}
		
		return this.counter(sql);
	}
}
